/*
 * Copyright (C) 2013 SeekDeepWithin.com
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package com.seekdeepwithin.DataAccess;

import com.almworks.sqlite4java.*;
import com.seekdeepwithin.*;
import com.seekdeepwithin.Controls.*;
import com.seekdeepwithin.Data.Book;
import java.io.*;
import java.util.*;
import java.util.List;
import javafx.scene.control.*;

/**
 * User: Jonathan Montiverdi Date: Sep 13, 2013
 *
 * Main database class.
 */
public class SdwDatabase
{
   public static final String DB_VERSION = "1.0";
   public static final String DB_PATH = "./Data/SdwDb.db";
   private final Map<Integer, String> m_Areas = new HashMap<> ();
   private final Map<Integer, String> m_StyleTypes = new HashMap<> ();
   private final List<Book> m_Books = new ArrayList<> ();
   private static SdwDatabase s_Instance;
   private SQLiteConnection m_Conn;

   /**
    * Private constructor to prevent improper use.
    */
   private SdwDatabase ()
   {
   }

   /**
    * Gets the service manager instance.
    * @return The one and only instance of the service manager.
    */
   public static SdwDatabase getInstance ()
   {
      if (s_Instance == null) {
         s_Instance = new SdwDatabase ();
      }
      return s_Instance;
   }

   /**
    * Initializes the database.
    */
   public void Init () throws SQLiteException
   {
      File f = new File (DB_PATH);
      boolean createDb = !f.exists ();
      this.m_Conn = new SQLiteConnection (f);
      this.m_Conn.open ();
      if (createDb) {
         MainController.getInstance ().showInfo ("Main database not found, creating a new one...", false,
                                                 InformationBar.ICON_INFO, 5);
         MainController.getInstance ().queueJob (new CreateDatabaseService ());
      } else {
         this.loadBooks ();
         this.loadAreas ();
         this.loadStyleTypes ();
      }
   }

   /**
    * Gets the list of books downloaded in the database.
    * @return The list of local books.
    */
   public List<Book> getBooks ()
   {
      return Collections.unmodifiableList (this.m_Books);
   }

   /**
    * Gets the area name from the given ID.
    * @param areaId ID of area to get name for.
    * @return The name of the requested area.
    */
   public String getAreaFromId (int areaId)
   {
      String area = "glossary";
      if (this.m_Areas.containsKey (areaId)) {
         area = this.m_Areas.get (areaId);
      }
      return area;
   }

   /**
    * Gets the area ID from the given name.
    * @param areaName Name of area to get ID for.
    * @return The ID of the given area.
    */
   public int getAreaId (String areaName)
   {
      int areaId = 1;
      for (Map.Entry<Integer, String> area : this.m_Areas.entrySet ()) {
         Integer id = area.getKey ();
         String name = area.getValue ();
         if (name.equalsIgnoreCase (areaName)) {
            areaId = id;
            break;
         }
      }
      return areaId;
   }

   /**
    * Gets the style type from the given ID.
    * @param styleTypeId ID of style type to get.
    * @return The requested style type.
    */
   public String getStyleType (int styleTypeId)
   {
      String styleType = "span";
      if (this.m_StyleTypes.containsKey (styleTypeId)) {
         styleType = this.m_StyleTypes.get (styleTypeId);
      }
      return styleType;
   }

   /**
    * Gets the book with the given name.
    * @param name Name of book to get.
    * @return The requested book, null if not found.
    */
   public Book getBook (String name)
   {
      Book b = null;
      for (Book book : this.m_Books) {
         if (book.getTitle ().equalsIgnoreCase (name)) {
            b = book;
         }
      }
      return b;
   }

   /**
    * Loads the current books in the database.
    */
   private void loadBooks ()
   {
      this.m_Books.clear ();
      try {
         SQLiteStatement statement = this.prepare ("SELECT book_id, title, subtitle, abbreviation, author, date, amazonid, has_chapters, has_subbooks, info, is_subbook, image FROM books WHERE NOT is_subbook = 1 ORDER BY title;");
         while (statement.step ()) {
            Book book = new Book (statement.columnString (1), statement.columnInt (0));
            book.setSubTitle (statement.columnString (2));
            book.setAbbreviation (statement.columnString (3));
            book.setAuthor (statement.columnString (4));
            book.setDate (statement.columnString (5));
            book.setAmazonId (statement.columnString (6));
            book.setHasChapters (statement.columnInt (7) == 1);
            book.setHasSubBooks (statement.columnInt (8) == 1);
            book.setInfo (statement.columnString (9));
            book.setIsSubBook (false);
            this.m_Books.add (book);
         }
      } catch (SQLiteException ex) {
         Dialogs.showErrorDialog (MainController.getInstance ().getStage (),
                                  "An error occured while loading the books from the main database.",
                                  "Database error - SdwDatabase.loadBooks ()", "Error", ex);
      }
   }

   /**
    * Loads the list of areas.
    */
   private void loadAreas ()
   {
      this.m_Areas.clear ();
      try {
         SQLiteStatement statement = this.prepare ("SELECT area_id, area FROM areas;");
         while (statement.step ()) {
            this.m_Areas.put (statement.columnInt (0), statement.columnString (1));
         }
         statement.dispose ();
      } catch (SQLiteException exception) {
         Dialogs.showErrorDialog (MainController.getInstance ().getStage (), "An error occured while getting the area list.",
                                  "Database error - SdwDatabase.loadAreas ()", "Error", exception);
      }
   }

   /**
    * Loads the list of style types.
    */
   private void loadStyleTypes ()
   {
      this.m_StyleTypes.clear ();
      try {
         SQLiteStatement statement = this.prepare ("SELECT style_type_id, style_type FROM style_types;");
         while (statement.step ()) {
            this.m_StyleTypes.put (statement.columnInt (0), statement.columnString (1));
         }
         statement.dispose ();
      } catch (SQLiteException exception) {
         Dialogs.showErrorDialog (MainController.getInstance ().getStage (), "An error occured while getting the style types.",
                                  "Database error - SdwDatabase.loadStyleTypes ()", "Error", exception);
      }
   }

   /**
    * Updates database data in memory.
    */
   public void update ()
   {
      this.loadBooks ();
      this.loadAreas ();
      this.loadStyleTypes ();
   }

   /**
    * Updates the book list from the database.
    */
   public void updateBooks ()
   {
      this.loadBooks ();
   }

   /**
    * Gets the ID for the given book and sub book.
    * @param bookId The ID of the book.
    * @param subBookId The ID of the sub book.
    * @param checkLinks If true, a check for a linked sub book will be performed.
    * @return The ID of the sub book and book combination, -1 if not found.
    * @throws SQLiteException
    */
   public int getSubBookListId (int bookId, int subBookId, boolean checkLinks) throws SQLiteException
   {
      int subBookListId = -1;
      SQLiteStatement statement = this.prepare ("SELECT sub_book_list_id FROM subbook_list"
                                                + " WHERE book_id = ? AND sub_book_id = ?;");
      statement.bind (1, bookId);
      statement.bind (2, subBookId);
      if (statement.step ()) {
         subBookListId = statement.columnInt (0);
      }
      statement.dispose ();

      if (checkLinks && subBookListId != -1) {
         int linkId = getSubBookLinkId (subBookListId);
         if (linkId != -1) {
            subBookListId = linkId;
         }
      }
      return subBookListId;
   }

   /**
    * Gets a link ID for the given sub book ID, if any exists.
    * @param subBookListId Sub book list ID to get link for.
    * @return The link ID, -1 if none exist.
    * @throws SQLiteException
    */
   public int getSubBookLinkId (int subBookListId) throws SQLiteException
   {
      int linkId = -1;
      SQLiteStatement statement = this.prepare ("SELECT sub_book_list_id_link FROM subbook_list_links"
                                                + " WHERE sub_book_list_id=?;");
      statement.bind (1, subBookListId);
      if (statement.step ()) {
         linkId = statement.columnInt (0);
      }
      statement.dispose ();
      return linkId;
   }

   /**
    * Gets the ID of the specified chapter.
    * @param bookId ID of book.
    * @param subBook Name of sub book.
    * @param chapter Name of chapter.
    * @return The ID of the specified chapter.
    * @throws SQLiteException
    */
   public int getChapterId (int bookId, String subBook, String chapter) throws SQLiteException
   {
      int chapterId = -1;
      int subBookId = this.getBookId (subBook);
      int subBookListId = this.getSubBookListId (bookId, subBookId, true);

      SQLiteStatement statement = this.prepare ("SELECT chapter_list.chapter_list_id"
                                                + " FROM chapter_list"
                                                + " INNER JOIN chapters ON chapter_list.chapter_id = chapters.chapter_id"
                                                + " INNER JOIN subbook_list ON chapter_list.sub_book_list_id = subbook_list.sub_book_list_id"
                                                + " WHERE chapters.name = ?"
                                                + " AND subbook_list.sub_book_list_id = ?");
      statement.bind (1, chapter);
      statement.bind (2, subBookListId);
      if (statement.step ()) {
         chapterId = statement.columnInt (0);
      }
      return chapterId;
   }

   /**
    * Gets the ID of the book with the given title.
    * @param title The title of the book to get.
    * @return The ID of the requested book.
    * @throws SQLiteException
    */
   public int getBookId (String title) throws SQLiteException
   {
      int bookId = -1;
      SQLiteStatement statement = this.prepare ("SELECT book_id FROM books"
                                                + " WHERE title=?;");
      statement.bind (1, title);
      if (statement.step ()) {
         bookId = statement.columnInt (0);
      }
      statement.dispose ();
      return bookId;
   }

   /**
    * Prepares a SQL statement to use with database.
    * @param command Command to perform against database.
    * @return The prepared statement.
    * @throws SQLiteException
    */
   public SQLiteStatement prepare (String command) throws SQLiteException
   {
      return this.m_Conn.prepare (command);
   }

   /**
    * Executes the given command.
    * @param command Command to execute.
    * @throws SQLiteException
    */
   public void exec (String command) throws SQLiteException
   {
      this.m_Conn.exec (command);
   }

   /**
    * Closes the database connection.
    */
   public void close () throws IOException
   {
      this.m_Conn.dispose ();
   }
}
